# database.py
import pymysql
import logging
from typing import List, Dict, Optional

logger = logging.getLogger('DatabaseManager')


class DatabaseManager:
    def __init__(self, host: str, user: str, password: str, database: str, port: int = 3306):
        self.host = host
        self.user = user
        self.password = password
        self.database = database
        self.port = port
        self.connection = None
        self.cursor = None

        # 创建必要的表
        self.create_tables()

    def connect(self):
        """连接到MySQL数据库"""
        if self.connection and self.connection.open:
            return True
        try:
            self.connection = pymysql.connect(
                host=self.host,
                user=self.user,
                password=self.password,
                database=self.database,
                port=self.port,
                charset='utf8mb4',
                cursorclass=pymysql.cursors.DictCursor
            )
            self.cursor = self.connection.cursor()
            logger.info(f"成功连接到数据库: {self.database}")
            return True
        except pymysql.Error as e:
            logger.error(f"连接数据库失败: {e}")
            return False

    def create_tables(self):
        """创建必要的表"""
        if not self.connect():
            return False

        try:
            # 创建分类表
            self.cursor.execute("""
                CREATE TABLE IF NOT EXISTS category (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '分类ID',
                    name VARCHAR(100) NOT NULL COMMENT '分类名称(如:宝宝巴士)',
                    media_type ENUM('video', 'audio', 'mixed') NOT NULL DEFAULT 'mixed' COMMENT '媒体类型',
                    UNIQUE KEY uniq_name (name)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多媒体分类表';
            """)

            # 创建专辑表
            self.cursor.execute("""
                CREATE TABLE IF NOT EXISTS album (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '专辑ID',
                    category_id INT UNSIGNED NOT NULL COMMENT '分类ID',
                    name VARCHAR(200) NOT NULL COMMENT '专辑名称(如:宝宝巴士启蒙音乐剧)',
                    original_name VARCHAR(200) NOT NULL COMMENT '原始目录名称',
                    `order` SMALLINT UNSIGNED DEFAULT 0 COMMENT '专辑排序序号',
                    episode_count SMALLINT UNSIGNED NOT NULL COMMENT '专辑集数(如:159)',
                    cover_url VARCHAR(255) COMMENT '专辑封面路径',
                    media_type ENUM('video', 'audio', 'mixed') NOT NULL DEFAULT 'mixed' COMMENT '媒体类型',
                    FOREIGN KEY (category_id) REFERENCES category(id) ON DELETE CASCADE,
                    INDEX idx_category (category_id)
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多媒体专辑表';
            """)

            # 创建多媒体资源表（增加is_uploaded字段）
            self.cursor.execute("""
                CREATE TABLE IF NOT EXISTS media (
                    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY COMMENT '资源ID',
                    album_id INT UNSIGNED NOT NULL COMMENT '所属专辑ID',
                    title VARCHAR(200) NOT NULL COMMENT '单集标题(如:奇妙的身体)',
                    episode_seq SMALLINT UNSIGNED NOT NULL COMMENT '单集序号(如:1表示第1集)',
                    `order` SMALLINT UNSIGNED DEFAULT 0 COMMENT '单集排序序号',
                    file_path VARCHAR(500) NOT NULL COMMENT '文件路径标识',
                    duration INT UNSIGNED COMMENT '时长(秒)',
                    media_type ENUM('video', 'audio') NOT NULL DEFAULT 'video' COMMENT '媒体类型',
                    file_size BIGINT UNSIGNED COMMENT '文件大小(字节)',
                    file_format VARCHAR(10) COMMENT '文件格式(mp4, mp3等)',
                    is_uploaded TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否上传成功(1:成功, 0:失败)',
                    FOREIGN KEY (album_id) REFERENCES album(id) ON DELETE CASCADE,
                    INDEX idx_album (album_id),
                    UNIQUE KEY uniq_file (file_path(200))  -- 前缀索引适应长路径
                ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='多媒体资源表';
            """)

            self.connection.commit()
            logger.info("数据库表创建完成或已存在")
            return True
        except pymysql.Error as e:
            logger.error(f"创建数据库表失败: {e}")
            return False

    def execute_import(self, sql_statements: List[str]):
        """执行SQL导入语句"""
        if not self.connect():
            return False
        try:
            for sql in sql_statements:
                logger.info(f"执行sql：{sql}")
                # 跳过注释和空行
                if sql.strip().startswith('--') or not sql.strip():
                    continue

                logger.debug(f"执行SQL: {sql[:100]}{'...' if len(sql) > 100 else ''}")
                self.cursor.execute(sql)

            self.connection.commit()
            logger.info("数据库导入成功完成")
            return True
        except pymysql.Error as e:
            logger.error(f"数据库导入失败: {e}")
            self.connection.rollback()
            return False

    def update_upload_status(self, file_path: str, is_uploaded: int):
        """更新文件上传状态"""
        if not self.connect():
            return False

        try:
            sql = """
                UPDATE media 
                SET is_uploaded = %s 
                WHERE file_path = %s
            """
            self.cursor.execute(sql, (is_uploaded, file_path))
            self.connection.commit()
            return self.cursor.rowcount > 0
        except pymysql.Error as e:
            logger.error(f"更新上传状态失败: {e}")
            return False

    def close(self):
        if self.connection:
            self.connection.close()
            self.connection = None
            logger.info("数据库连接已关闭")
